package src.day20180418;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by 林云志 on 2018/4/23.
 */
public class TestReadPOI07 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

        class Student{
            Integer studentNumber;
            String financeCode;
            String year;
            String paidFor;
            double shouldPay;
            double actuallyPay;
            double lackPay;
            public Student(){

            }
            public Student(int studentNumber, String financeCode, String year, String paidFor, double shouldPay, double actuallyPay, double lackPay) {
                this.studentNumber = studentNumber;
                this.financeCode = financeCode;
                this.year = year;
                this.paidFor = paidFor;
                this.shouldPay = shouldPay;
                this.actuallyPay = actuallyPay;
                this.lackPay = lackPay;
            }
        }
        //3+1
        Connection connection = null;
        Statement statement = null;

//        String sql = "insert into paymentinfo values()";

        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection(
                "jdbc:mysql://120.78.82.65/sctu?useUnicode=true&characterEncoding=utf-8&useSSL=false",
                "automannn",
                "chenkaihai...11"
        );
        statement = connection.createStatement();


        FileInputStream inputStream = new FileInputStream(new File("E:\\迎新管理系统\\迎新报到子系统\\数据导入模板\\交费信息导入模板.xlsx"));
        //读取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //读取工作表
        XSSFSheet sheet = workbook.getSheetAt(0);
        List<String> list = new ArrayList<String>();
        Student student = new Student();
        for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
            List<Student> listi = new ArrayList<>();
            String value = null;
            //读取行
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                //读取单元格
                XSSFCell cell = row.getCell(j);
                if (row.getCell(j)==null){
                        continue;
                    }
                    else {
                        if (j==0){
                            student.studentNumber = Integer.parseInt(cell.getStringCellValue());
                        }
                        if (j==1){
                            student.financeCode = cell.getStringCellValue();
                        }
                        if (j==2){
                            student.year = cell.getStringCellValue();
                        }
                        if (j==3){
                            student.paidFor = cell.getStringCellValue();
                        }
                        if (j==4){
                            student.shouldPay = Double.parseDouble(cell.getStringCellValue());
                        }
                        if (j==5){
                            student.actuallyPay = Double.parseDouble(cell.getStringCellValue());
                        }
                        if (j==6){
                            student.lackPay = Double.parseDouble(cell.getStringCellValue());
                        }
                        else {
                            continue;
                        }
                    }
            }
            listi.add(student);

                String sql1 = "insert into paymentinfo values("+student.studentNumber+",'"+student.financeCode+"','"+student.year+"','"+student.paidFor+"','"+student.shouldPay+"','"+student.actuallyPay+"','"+student.lackPay+"')";
                Boolean resultSet = statement.execute(sql1);
                for (Student n:listi){
                    System.out.println(n);
                }
        }
        System.out.println();

        inputStream.close();
        workbook.close();

    }}